﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Time.DB;
using Time.Entity;
using Time.IDAL;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;

namespace Time.DAL
{
    public class EqAlarm_DAL : IEqAlarm_DAL
    {   
        //记录日志
        public static log4net.ILog logger = log4net.LogManager.GetLogger("debug");
        static DbUtil objDbUtil;
        public EqAlarm_DAL()
        {
            objDbUtil = new DbUtil();
        }
        public EqAlarm_DAL(string bsType)
        {
            objDbUtil = new DbUtil(bsType);
        }
        /// <summary>
        /// 获取数据集合
        /// </summary>
        /// <returns></returns>
        public DataTable getData(string sqlStr)
        {
            DataTable tempDt = new DataTable();
            try
            {       
                tempDt = objDbUtil.ExecuteDataTable(sqlStr, null);
            }
            catch (Exception ex)
            {
                logger.Error("error:" + ex.Message);
            }
            return tempDt;
        }
        /// <summary>
        /// 参数处理
        /// </summary>
        /// <param name="Option">参数数组</param>
        /// <param name="OutCol">输出参数</param>
        /// <returns></returns>
        private DbParameter[] dbParameter(string[,] Option)
        {
            if (Option.Length < 1)
            {
                return null;
            }
            int lenth = Option.Length / 2;

            SqlParameter[] sqlParameter = new SqlParameter[lenth];

            for (int i = 0; i < Option.Length / 2; i++)
            {
                sqlParameter[i] = new SqlParameter(Option[i, 0].ToString(), Option[i, 1].ToString());
                sqlParameter[i].Direction = ParameterDirection.InputOutput;
                sqlParameter[i].SourceColumn = Option[i, 1].ToString();
                sqlParameter[i].SourceVersion = DataRowVersion.Original;
            }
            return sqlParameter;
        }


        /// <summary>
        /// 执行SQL处理
        /// </summary>
        /// <param name="strSql">执行的SQL语句或者存储过程名</param>
        /// <param name="Option">参数数据</param> 
        /// <returns></returns>
        public int ExecuteNonQuery(string strSql, string[,] Option)
        {
            try
            {
                Time.Foramework.ConfigOperate.PrintLog(strSql);
                 IList<DbParameter> objPa = null;
                if (Option != null)
                {
                    objPa = dbParameter(Option);
                }
             
                return objDbUtil.ExecuteNonQuery(strSql, objPa);
            }
            catch(Exception ex)
            {
                Time.Foramework.ConfigOperate.PrintLog(strSql + "\r\n 错误：" + ex.Message.ToString ());
                
            }
            return 0;
        }
        /// <summary>
        /// 启用事务处理多条SQL语句
        /// </summary>
        /// <param name="listStrSql"></param>
        /// <returns></returns>
        public int ExecuteNonQueryTransaction(List<string> listStrSql)
        {
            return objDbUtil.ExecuteNonQueryTransaction(listStrSql);
        }

        /// <summary>
        /// 获取数据集合
        /// </summary>
        /// <returns></returns>
        public DataTable getDataTable(string tableName)
        {
            DataTable tempDt = new DataTable();
            
            DbUtil objDbUtil = new DbUtil();
            string sqlStr = ""; 
            sqlStr = string.Format("select Distinct * from {0} ", tableName); 
            try
            {
                tempDt = objDbUtil.ExecuteDataTable(sqlStr, null);
            }
            catch (Exception ex)
            {
                logger.Error("error:" + ex.Message);
            }
            return tempDt;
        }
        public DataTable getDataTableByMap( string tableName, string field, string orderInfo = "MCGS_Time",string BSType= "SqlServer")
        {
            DataTable tempDt = new DataTable();     
            string sqlStr   = string.Format("select Distinct {0} from {1} order by {2} desc", field, tableName, orderInfo);
            try
            {
                objDbUtil = new DbUtil(BSType);
                tempDt = objDbUtil.ExecuteDataTable(sqlStr, null);
            }
            catch (Exception ex)
            {

                logger.Error("error:" + ex.Message);
            }
            return tempDt;
        }
        /// <summary>
        /// 获取需要转移数据的表名称列表
        /// </summary>
        /// <returns></returns>
        public DataTable getTableObj()
        {
            DataTable tempDt = new DataTable();
            DbUtil objDbUtil = new DbUtil();
            //SQLServerHelp objSqlHelp = new SQLServerHelp();

            string sqlStr = "select name from sysobjects where xtype='u'and name<>'HK_EqProductData' order by name asc";
            try
            {
                tempDt = objDbUtil.ExecuteDataTable(sqlStr, null);
            }
            catch (Exception ex)
            {
                logger.Error("error:" + ex.Message);
            }
            return tempDt;
        }
        /// <summary>
        /// 数据插入到test,不删除数据源
        /// </summary>
        /// <param name="objDt">数据表</param>
        /// <returns></returns>
        public int MoveData(string TableName, DataTable objDt)
        {
            if (objDt.Rows.Count < 1)
            {
                return 0;
            }
            DbUtil objDbUtil = new DbUtil(); 
            if (SQLServerHelp.SqlBulkCopyByDatatable(TableName, objDt))
            {
                string sqlStr = string.Format("delete from {0} where Date_Time between @startDt and @EndDt", TableName);
                OleDbParameter[] p ={ new OleDbParameter("@startDt",OleDbType.Date) { Value = "2000/01/01"},
                                  new OleDbParameter("@EndDt",OleDbType.Date){ Value = objDt.Rows[0]["Date_Time"].ToString ()}
                                    };          //顺序需要一一对应
                OleDBHelp objOleDBHelp = new OleDBHelp();
                if(objOleDBHelp.ExcOperationData(sqlStr,p))
                {
                    return 1;
                }
            }

            return 0;
        }
        public int MoveDataByMap(string DestBsType, string STableName, string DTableName, DataTable objDt, string[] SourceFieldArr, string[] DestFieldArr)
        {
            if (objDt.Rows.Count < 1)
            {
                return 0;
            }
            DbUtil objDbUtil = new DbUtil(DestBsType);
            SQLServerHelp.SqlBulkCopyByDatatableMap(DTableName, objDt, SourceFieldArr, DestFieldArr);

            return 0;
        }
        /// <summary>
        /// 转移数据后删除数据源
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="objDt"></param>
        /// <returns></returns>
        public int MoveDataAndDelete(string TableName, DataTable objDt)
        {
            if (objDt.Rows.Count < 1)
            {
                return 0;
            }
            DbUtil objDbUtil = new DbUtil(); 
            if (SQLServerHelp.SqlBulkCopyByDatatable(TableName, objDt))
            {

                string sqlStr = string.Format("delete from {0} where Date_Time between @startDt and @EndDt", TableName);
                OleDbParameter[] p ={ new OleDbParameter("@startDt",OleDbType.Date) { Value = "2016/01/01"},
                                  new OleDbParameter("@EndDt",OleDbType.Date){ Value =DateTime.Now.AddSeconds (-2).ToString("yyyy-MM-dd HH:mm:ss")}
                                    };          //顺序需要一一对应
                OleDBHelp objOleDBHelp = new OleDBHelp();
                if (objOleDBHelp.ExcOperationData(sqlStr, p))
                {
                    return 1;
                }
                
            }
            return 0;
        }
        public int ExecuteNonQuery(string DestBsType, string strSql, string[,] Option)
        {
            DbUtil objDbUtil = new DbUtil();
            if (!string.IsNullOrEmpty(DestBsType))
            {
                objDbUtil = new DbUtil(DestBsType);
            }
            IList<DbParameter> objPa = dbParameter(Option);
            return objDbUtil.ExecuteNonQuery(strSql, objPa);
        }
        public int MoveDataMapAndDelete( string STableName, string DTableName, DataTable objDt, string[] SourceFieldArr, string[] DestFieldArr)
        {
            if (objDt.Rows.Count < 1)
            {
                return 0;
            }
            objDbUtil = new DbUtil();
            DataTable objDtDel = objDt.Copy();
            Boolean sbcopyrtn = SQLServerHelp.SqlBulkCopyByDatatableMap(DTableName, objDt, SourceFieldArr, DestFieldArr);

            //必须插入成功了，才能删除
            if (sbcopyrtn)
            { 
                string sqlStr = null;
                OleDBHelp objOleDBHelp = new OleDBHelp(); 
                if (STableName.ToUpper().Equals("MCGS_ALARMINFO"))
                {
                    sqlStr = string.Format("delete from  MCGS_ALARMINFO where TimeS  <= @mcgsTime ");
                    OleDbParameter[] p = { new OleDbParameter("@mcgsTime", OleDbType.Date) { Value = DateTime.Now.AddSeconds (-1).ToString () } };
                    objOleDBHelp.ExcOperationData(sqlStr, p);
                }
                else
                {
                    sqlStr = string.Format("delete from {0} where MCGS_Time <= @mcgsTime ", STableName);
                    OleDbParameter[] p = { new OleDbParameter("@mcgsTime", OleDbType.Date) { Value = DateTime.Now.AddSeconds(-1).ToString() } };
                    objOleDBHelp.ExcOperationData(sqlStr, p);
                }
                return 1;
            }

            return 0;
        }
    }
}
